package common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import db.DBManager;

public class Order 
{
	private int id;
	private String num;
	private String customer_code;
	private Date date;
	private int cost_total;
	private int price_total;
	private int fee;
	private Date deliver;
	private int judge;
	private int receipt_flag;
	
	private String item_code;
	private int orders;
	
	/**
	 * 納品書発行未処理の受注一覧を取得する。
	 * @return	未処理の受注情報の配列
	 */
	public static List<Order> find_not_yet()
	{
		Connection con = DBManager.getConnection();
		List<Order> list = new ArrayList<Order>();
		try
		{
			String sql = "select info.num as order_num,"		//名前を変更
						+ " info.customer_code,"
						+ " order_item.item_code,"
						+ " order_item.orders"
					+ " from order_info as info"		//order_infoの名前をinfoで扱う
					+ " inner join order_item"
					+ " on info.num = order_item.order_num"
					+ " where info.receipt_flag = 0"
					+ " order by info.num, order_item.num";
			Statement stm = con.createStatement();
			ResultSet rs = stm.executeQuery(sql);
			while(rs.next())
			{
				Order o = new Order();
				o.setNum(rs.getString("order_num"));								//受注番号
				o.setCustomer_code(rs.getString("customer_code"));		//顧客番号
				o.setItem_code(rs.getString("item_code"));						//商品コード
				o.setOrders(rs.getInt("orders"));										//注文数
				list.add(o);
			}
			rs.close();
			stm.close();
			con.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	public static List<Order> find_not_yet(int s)
	{
		Connection con = DBManager.getConnection();
		int start = s;
		List<Order> list = new ArrayList<Order>();
		try
		{
			String sql = "select info.num as order_num,"		//名前を変更
						+ " info.customer_code,"
						+ " order_item.item_code,"
						+ " order_item.orders"
					+ " from order_info as info"		//order_infoの名前をinfoで扱う
					+ " inner join order_item"
					+ " on info.num = order_item.order_num"
					+ " where info.receipt_flag = 0"
					+ " order by info.num, order_item.num"
					+ " limit ?, 10";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt( 1, start);
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				Order o = new Order();
				o.setNum(rs.getString("order_num"));								//受注番号
				o.setCustomer_code(rs.getString("customer_code"));		//顧客番号
				o.setItem_code(rs.getString("item_code"));						//商品コード
				o.setOrders(rs.getInt("orders"));										//注文数
				list.add(o);
			}
			rs.close();
			ps.close();
			con.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 納品書発行未処理の受注一覧を取得する。
	 * @return	未処理の受注情報の配列
	 */
	public static List<String> find_not_yet_order()
	{
		Connection con = DBManager.getConnection();
		List<String> list = new ArrayList<String>();
		try
		{
			String sql = "select num "		//名前を変更
					+ " from order_info"		//order_infoの名前をinfoで扱う
					+ " where receipt_flag = 0"
					+ " order by num";
			Statement stm = con.createStatement();
			ResultSet rs = stm.executeQuery(sql);
			while(rs.next())
			{
				list.add(rs.getString("num"));				//受注番号	
			}
			rs.close();
			stm.close();
			con.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	public static List<String> find_not_yet_order(int s)
	{
		Connection con = DBManager.getConnection();
		int start = s;
		List<String> list = new ArrayList<String>();
		try
		{
			String sql = "select num "		//名前を変更
					+ " from order_info"		//order_infoの名前をinfoで扱う
					+ " where receipt_flag = 0"
					+ " order by num"
					+ " limit ?, 10";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt( 1, start);
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				list.add(rs.getString("num"));
			}
			rs.close();
			ps.close();
			con.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
		/**
		 * 納品書発行未処理を発行済に更新する。
		 * @return 納品書発行結果
		 */
	public boolean setReceiptFlag()
	{
		Connection con = DBManager.getConnection();
		boolean ret = false;
//		・納品書発行フラグを(1)にする。
		try
		{
			String sql = "update order_info"
					+ " set receipt_flag = 1"
					+ " where num =" + this.num;			//受注番号
			Statement stm = con.createStatement();
			stm.executeUpdate(sql);
			ret = true;
			
			stm.close();
			con.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return ret;
	}

	/**
	 * 納品書発行未処理を発行済に更新する。
	 * @return 納品書発行結果
	 */
public static boolean setReceiptFlagByOrderNumber(String number)
{
	Connection con = DBManager.getConnection();
	boolean ret = false;
//	・納品書発行フラグを(1)にする。
	try
	{
		String sql = "update order_info"
				+ " set receipt_flag = 1"
				+ " where num =" + number;			//受注番号
		Statement stm = con.createStatement();
		stm.executeUpdate(sql);
		ret = true;
		
		stm.close();
		con.close();
	}
	catch(SQLException e)
	{
		e.printStackTrace();
	}
	return ret;
}
	
	/**
	 * カート情報を受注明細テーブルと受注情報テーブルに登録する
	 * @param cart
	 * @return	受注番号
	 * @throws SQLException
	 */
	public static String  add(Cart cart)
	{
		Connection con = DBManager.getConnection();
		List<OrderItem> list = cart.getItems();
//		int cost_total = 0;
//		int price_total = 0;
		try
		{
			Statement smt = con.createStatement();
			for( int i=0; i < list.size(); i++)
			{
				OrderItem o = list.get(i);
				Item item = Item.find_by_code(o.getItem_code());
				String sqlItem = "insert into order_item"
						+ "(order_num,num,item_code,cost,price,orders)"
						+ "values ( '"
						+ cart.getOrder_num()+"',"		//受注番号
						+ (i + 1) + ",'"								//行番号
						+ o.getItem_code() +"',"			//商品番号
						+ item.getCost() +","					//原価
						+ item.getPrice() +","					//売価
						+ o.getOrders() +""					//注文数
						+ ")";
				smt.executeUpdate(sqlItem);
//				cost_total = cost_total + item.getCost();
//				price_total = price_total + item.getPrice();
			}
//			int fee = 500;
//			if(price_total >= 10000) fee = 0;
			String sqlInfo = "insert into order_info"
					+ "(num, customer_code, date, deliver, cost_total, price_total, fee, judge, receipt_flag ) "
					+ " values (?,?,?,?,?,?,?,?,?)";
			PreparedStatement ps = con.prepareStatement(sqlInfo);
			ps.setString(1, cart.getOrder_num());									//受注番号
			ps.setString(2, cart.getCs().getCode());								//顧客番号
			ps.setDate(3, new java.sql.Date(System.currentTimeMillis()));			//受注日
			ps.setDate(4, new java.sql.Date(cart.getDeli().getTime()));				//配達予定日
			ps.setInt(5, cart.getCost_total());										//合計原価
			ps.setInt(6, cart.getPrice_total());									//合計売価
			ps.setInt(7,cart.getFee());												//運賃
			ps.setInt(8, cart.getJudge());											//与信結果
			ps.setInt(9,0);															//納品書発行フラグ

			ps.executeUpdate();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return cart.getOrder_num();
	}

	public int getId() {
		return id;
	}


	public void setId(int id) {
		this.id = id;
	}


	public String getNum() {
		return num;
	}


	public void setNum(String num) {
		this.num = num;
	}


	public String getCustomer_code() {
		return customer_code;
	}


	public void setCustomer_code(String customer_code) {
		this.customer_code = customer_code;
	}


	public Date getDate() {
		return date;
	}


	public void setDate(Date date) {
		this.date = date;
	}


	public int getCost_total() {
		return cost_total;
	}


	public void setCost_total(int cost_total) {
		this.cost_total = cost_total;
	}


	public int getPrice_total() {
		return price_total;
	}


	public void setPrice_total(int price_total) {
		this.price_total = price_total;
	}


	public int getFee() {
		return fee;
	}


	public void setFee(int fee) {
		this.fee = fee;
	}


	public Date getDeliver() {
		return deliver;
	}


	public void setDeliver(Date deliver) {
		this.deliver = deliver;
	}


	public int getJudge() {
		return judge;
	}


	public void setJudge(int judge) {
		this.judge = judge;
	}


	public int getReceipt_flag() {
		return receipt_flag;
	}


	public void setReceipt_flag(int receipt_flag) {
		this.receipt_flag = receipt_flag;
	}


	public String getItem_code() {
		return item_code;
	}


	public void setItem_code(String item_code) {
		this.item_code = item_code;
	}


	public int getOrders() {
		return orders;
	}


	public void setOrders(int orders) {
		this.orders = orders;
	}

	

	
	
}
